﻿
namespace MSSQL.Implement
{
    using Dapper;
    using Enums;
    using Interface;
    using PagingCorrelation;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Reflection;
    using System.Text;
    using System.Threading.Tasks;
    using ToolsModel;


    /// <summary>
    /// 该类实现了查询功能
    /// </summary>
    public class QueryData : AbstractDataQuery
    {
        #region 分页语句

        /// <summary>
        /// 分页用到的sql语句
        /// </summary>
        private const string PAGINATION_SQL = @"DECLARE	@MaxCount INT 
                                        SELECT	@MaxCount = COUNT (1)	FROM ({0}) AS forMaxCount--最大条数
                                        SELECT	*,@MaxCount MaxCount
		                                        FROM(
				                                        SELECT
					                                        ROW_NUMBER () OVER (ORDER BY {1}) AS ROWNUM ,*
				                                        FROM
					                                        ({0}) AS ret
			                                        ) AS query
		                                        WHERE
			                                        query.ROWNUM BETWEEN {2}
		                                        AND {3};";
        #endregion

        /// <summary>
        /// 需要实现的查询多条数据方法
        /// </summary>
        /// <typeparam name="T">查询的实体类型</typeparam>
        /// <param name="connection">连接对象</param>
        /// <param name="sqlCommand">SQL命令</param>
        /// <param name="parameter">参数</param>
        /// <returns>查询结果</returns>
        public override IList<T> QueryListDataImpl<T>(IDbConnection connection, string sqlCommand, object parameter)
        {
            // 获取查询结果

            var ret = connection.Query(
                                    sqlCommand,
                                    parameter,
                                    null,
                                    true,
                                    null,
                                    CommandType.Text).

                                    // 将结果集转换为字典

                                    Select(
                                        m => ((IDictionary<string, object>)m).

                                    // 封装字典数据     

                                    ToDictionary(
                                        kv => kv.Key,
                                        kv => kv.Value)).

                                    //转换为字典集合

                                    ToList<IDictionary<string, object>>();

            // 如果泛型类型是字典，泛型集合就是字典集合，否则就是设定的泛型类型集合
            var interfaceType = typeof(T).GetInterface("IDictionary");

            if (typeof(T).Name.IndexOf("IDictionary") >= 0 || interfaceType != null)
            {
                return ret.Select(m => (T)m).ToList();
            }
            else
            {
                return ret.Select(keyValue =>
                {

                    var properties = typeof(T).GetProperties();

                    var item = (T)(typeof(T).GetConstructor(new Type[] { }).Invoke(default(Type[])));

                    Parallel.ForEach(properties, property =>
                    {
                        object value = null;

                        var filedInfo = property.GetCustomAttributes(false).Where(f => f.GetType().Name == "FieldInfo").FirstOrDefault();

                        if (null != filedInfo)
                        {
                            var dataFiledName = filedInfo.GetType().GetProperty("ColumnName").GetValue(filedInfo).ToString();
                            if (keyValue.TryGetValue(dataFiledName, out value))
                            {
                                property.SetValue(item, value);
                            }
                        }
                    });
                    return item;
                }).

                //转为返回的结果集

                ToList<T>();
            }
        }

        /// <summary>
        /// 需要实现的实际分页查询的方法
        /// </summary>
        /// <typeparam name="T">查询的实体类型</typeparam>
        /// <param name="connection">连接对象</param>
        /// <param name="pageInfo">查询分页条件</param>
        /// <param name="sqlCommand">SQL命令</param>
        /// <param name="parameter">参数</param>
        /// <returns>查询结果</returns>
        public override PagingResult<T> QueryPaginationDataImpl<T>(IDbConnection connection, PageInfo pageInfo, string sqlCommand, object parameter)
        {
            int startPage = pageInfo.PageIndex == 0 ?
                            1 :
                            pageInfo.PageSize * (pageInfo.PageIndex - 1) + 1,   //开始条数
            endPage = startPage + pageInfo.PageSize - 1;                        //结束条数
            var result = new PagingResult<T>();                                 //分页结果

            sqlCommand = sqlCommand.EndsWith(";") ? sqlCommand.TrimEnd(';') : sqlCommand;

            // 分页查询语句
            string sql = string.Format(PAGINATION_SQL,
                                        sqlCommand,
                                        pageInfo.OrderBy == string.Empty ? "(SELECT 0)" :
                                            (string.Format("{0} {1}", pageInfo.OrderBy, pageInfo.orderType == OrderType.ASC ? "ASC" : "DESC")),
                                        startPage,
                                        endPage);

            // 获取查询结果（DapperRow[类型是IEnumerable<dynamic>]）,并将其转换为字典
            var ret = connection.Query(
                                        sql,
                                        parameter,
                                        null,
                                        true,
                                        null,
                                        CommandType.Text).
                                  Select(
                                        m => ((IDictionary<string, object>)m).
                                        ToDictionary(pi => pi.Key, pi => pi.Value)).
                                  ToList<IDictionary<string, object>>();

            if (ret.Count > 0)
            {
                // 随意抽一条取最大条数记录
                result.MaxCount = Convert.ToInt32(ret[0]["MaxCount"]);

                // 如果泛型类型是字典，分页结果里面的泛型集合就是字典集合，否则就是设定的泛型类型集合
                var interfaceType = typeof(T).GetInterface("IDictionary");

                if (typeof(T).Name.IndexOf("IDictionary") >= 0 || interfaceType != null)
                {
                    result.Result = ret.Select(m => (T)m).ToList();
                }
                else
                {
                    result.Result = ret.Select(keyValue =>
                    {
                        var properties = typeof(T).GetProperties();
                        var item = (T)(typeof(T).GetConstructor(new Type[] { }).Invoke(default(Type[])));

                        Parallel.ForEach(properties, property =>
                        {
                            object value = null;

                            var filedInfo = property.GetCustomAttributes(false).Where(f => f.GetType().Name == "FieldInfo").FirstOrDefault();

                            if (null != filedInfo)
                            {
                                var dataFiledName = filedInfo.GetType().GetProperty("ColumnName").GetValue(filedInfo).ToString();
                                if (keyValue.TryGetValue(dataFiledName, out value))
                                {
                                    property.SetValue(item, value);
                                }
                            }
                        });

                        return item;
                    }).ToList<T>();
                }
            }
            else
            {
                result.Result = new List<T>();
            }
            return result;
        }

        /// <summary>
        /// 需要实现的单条查询
        /// </summary>
        /// <typeparam name="T">查询的实体类型</typeparam>
        /// <param name="connection">连接对象</param>
        /// <param name="sqlCommand">SQL命令</param>
        /// <param name="param">参数</param>
        /// <returns>查询结果</returns>
        public override T QuerySingleDataImpl<T>(IDbConnection connection, string sqlCommand, object parameter)
        {
            return GetSingleEntity<T>(connection, sqlCommand, parameter);
        }


        /// <summary>
        /// 单条查询
        /// </summary>
        /// <typeparam name="T">查询的实体类型</typeparam>
        /// <param name="connection">连接对象</param>
        /// <param name="gCode">ID</param>
        /// <returns>查询结果</returns>
        public override T QuerySingleByCodeImpl<T>(IDbConnection connection, string gCode)
        {
            //从数据查询结果集
            var parameters = new { gCode = gCode };
            var tableName = typeof(T).GetCustomAttribute<TableInfo>().TableName;//数据表名称
            string sqlCommand = string.Format("SELECT * FROM {0} WHERE gCode=@gCode", tableName);
            return GetSingleEntity<T>(connection, sqlCommand);
        }


        /// <summary>
        /// 单条查询
        /// </summary>
        /// <typeparam name="T">查询的实体类型</typeparam>
        /// <param name="connection">连接对象</param>
        /// <param name="gCode">ID</param>
        /// <returns>查询结果</returns>
        public override T QuerySingleByWhereImpl<T>(IDbConnection connection, string sWhere, object parameter)
        {
            //从数据查询结果集
            var tableName = typeof(T).GetCustomAttribute<TableInfo>().TableName;//数据表名称
            string sqlCommand = string.Format("SELECT * FROM {0} WHERE 1=1 and {1}", tableName, sWhere);
            return GetSingleEntity<T>(connection, sqlCommand, parameter);

        }

        /// <summary>
        /// 获取单条结果实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connection"></param>
        /// <param name="sqlCommand"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        private T GetSingleEntity<T>(IDbConnection connection, string sqlCommand, object parameter = null)
        {
            var ret = connection.Query(
                               sqlCommand,
                               parameter,
                               null,
                               true,
                               null,
                               CommandType.Text).

                         // 将结果集转换为字典

                         Select(
                               m => ((IDictionary<string, object>)m).
                                                       ToDictionary(
                                                               pi => pi.Key,
                                                               pi => pi.Value)).

                         // 获取结果集

                         FirstOrDefault<IDictionary<string, object>>();
            if (ret == default(IDictionary<string, object>))

                return default(T);

            // 如果泛型类型是字典，结果就是字典，否则就是设定的泛型类型
            var interfaceType = typeof(T).GetInterface("IDictionary");

            if (typeof(T).Name.IndexOf("IDictionary") >= 0 || interfaceType != null)
            {
                return (T)ret;
            }
            else
            {
                var properties = typeof(T).GetProperties();
                var item = (T)(typeof(T).GetConstructor(new Type[] { }).Invoke(default(Type[])));
                Parallel.ForEach(properties, property =>
                {

                    object value = null;

                    var filedInfo = property.GetCustomAttributes(false).Where(f => f.GetType().Name == "FieldInfo").FirstOrDefault();

                    if (null != filedInfo)
                    {
                        var dataFiledName = filedInfo.GetType().GetProperty("ColumnName").GetValue(filedInfo).ToString();
                        if (ret.TryGetValue(dataFiledName, out value))
                        {
                            property.SetValue(item, value);
                        }
                    }
                });
                return item;
            }
        }

    }
}
